# coding=utf-8

import calendar
import datetime
from flask import current_app
from app import oraConnAC1
from app import oraConnAC2
from app import oraConnAC3
from app import oraConnAC4
from app import oraConnCEN
from app import oraConnPARAM

import os

from app.execute_sql import execute_sql

os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'


def get_datas(name, pra_sql):

    resultOut = []
    oraConn = {1: oraConnAC1, 2: oraConnAC2, 3: oraConnAC3, 4: oraConnAC4, 5: oraConnCEN, 6: oraConnPARAM}
    CEN = 5
    PARAM = 6

    # 获取当前月份
    currentMonStr = getCurrentMon()
    # 获取上月月份
    lastMonStr = getLastMon()
    # 获取上月 年月份
    lastYearMonStr = getLastYearMon()

    # 获取当前月第一天
    firstDay = getMonthFirstDay()
    firstDayStr = firstDay.strftime('%Y%m%d')
    # 获取当前月第二天
    tomorrow = firstDay + datetime.timedelta(days=1)
    # 获取当前月最后一天
    lastDay = getMonthLastDay()
    lastDayStr = lastDay.strftime('%m')
    # 获取上月最后一天(yyyymmdd)
    lastMonLastDayStr = getLastYearMonDay()

    current_app.logger.info(lastMonLastDayStr)

    if name == 'NetnpTrans':
        try:
            # 统计待处理工单数
            sql = pra_sql[0]
            param = {'param1': 'U', 'param2': '7070'}
            rs = execute_sql(oraConn.get(CEN), sql, param)
            count_7070 = rs[0][0]

            sql = pra_sql[0]
            param = {'param1': 'U', 'param2': '7080'}
            rs = execute_sql(oraConn.get(CEN), sql, param)
            count_7080 = rs[0][0]

            # 统计处理成功工单数
            sql = pra_sql[1]
            param = {'param1': 'S', 'param2': '7070', 'param3': lastDay}
            rs = execute_sql(oraConn.get(CEN), sql, param)
            succ_count_7070 = rs[0][0]

            sql = pra_sql[1]
            param = {'param1': 'S', 'param2': '7080', 'param3': lastDay}
            rs = execute_sql(oraConn.get(CEN), sql, param)
            succ_count_7080 = rs[0][0]

            # 统计处理失败工单数
            sql = pra_sql[2]
            param = {'param1': lastDay}
            rs = execute_sql(oraConn.get(CEN), sql, param)
            err_count = rs[0][0]

            # 组织返回数据
            datas = (count_7070, count_7080, succ_count_7070, succ_count_7080, err_count)
        except Exception as e:
            current_app.logger.error('----------------------------Exception e.message: %s', e.message)
            raise Exception(e.message)
        else:
            return datas

    elif name == 'check_balance_before_0':
        try:
            for i in range(0, 4):
                sql = pra_sql[i] + lastDayStr
                rs = execute_sql(oraConn.get(i + 1), sql)
                count = rs[0][0]
                resultOut.append(count)
            current_app.logger.info(resultOut)
        except Exception as e:
            current_app.logger.error('----------------------------Exception e.message: %s', e.message)
            raise Exception(e.message)

    elif name == 'check_balance_after_0':
        try:
            for i in range(0, 4):
                sql = pra_sql[i][0] + lastDayStr + pra_sql[i][1]
                param = {'param': firstDay}
                rs = execute_sql(oraConn.get(i + 1), sql, param)
                count = rs[0][0]
                resultOut.append(count)
            current_app.logger.debug(resultOut)
        except Exception as e:
            current_app.logger.error('----------------------------Exception e.message: %s', e.message)
            raise Exception(e.message)

    elif name == 'check_two_balance':
        try:
            for i in range(0, 4):
                check_result = []

                sql = pra_sql[i][0] + lastMonStr + pra_sql[i][1] + ' MINUS ' + pra_sql[i][0] + currentMonStr + pra_sql[i][2]
                param = {'param1': firstDay, 'param2': tomorrow}
                rs = execute_sql(oraConn.get(i + 1), sql, param)

                if len(rs) == 0:
                    count_1 = 0
                else:
                    count_1 = 1

                sql = pra_sql[i][0] + lastMonStr + pra_sql[i][2] + ' MINUS ' + pra_sql[i][0] + currentMonStr + pra_sql[i][1]
                param = {'param1': firstDay, 'param2': tomorrow}
                rs = execute_sql(oraConn.get(i + 1), sql, param)

                if len(rs) == 0:
                    count_2 = 0
                else:
                    count_2 = 1

                if count_1 == 0 and count_2 == 0:
                    count = 0
                else:
                    count = 1

                check_result.append(count)
                resultOut.append(check_result)
            current_app.logger.info(resultOut)
        except Exception as e:
            current_app.logger.error('----------------------------Exception e. message: %s', e.message)
            raise Exception(e.message)

    elif name == 'check_cycle':
        try:
            sql = pra_sql
            param = {'param': firstDayStr}
            rs = execute_sql(oraConn.get(PARAM), sql, param)

            if rs[0][0] == 20:
                count_0 = 0
            else:
                count_0 = 1

            if rs[1][0] == 20:
                count_1 = 0
            else:
                count_1 = 1

            if rs[2][0] == 20:
                count_2 = 0
            else:
                count_2 = 1

            if rs[3][0] == 20:
                count_3 = 0
            else:
                count_3 = 1

            resultOut = [firstDayStr, count_0, count_1, count_2, count_3]
            current_app.logger.info(resultOut)
        except Exception as e:
            current_app.logger.error('----------------------------Exception e.message: %s', e.message)
            raise Exception(e.message)

    elif name == 'check_indb_table':

        try:
            for j in range(0, 4):
                check_result = []
                for i in range(0, 12):
                    if i == 0:
                        sql = pra_sql[j][i] + lastYearMonStr
                        param = {}
                    elif i == 4:
                        sql = pra_sql[j][i]
                        param = {}
                    elif i == 5 or i == 6 or i == 7:
                        sql = pra_sql[j][i]
                        param = {'param': currentMonStr}
                    elif i == 8 or i == 9 or i == 10:
                        sql = pra_sql[j][i]
                        param = {'param': firstDayStr}
                    elif i == 11:
                        sql = pra_sql[j][i]
                        param = {'param': firstDay}
                    else:
                        sql = pra_sql[j][i] + currentMonStr
                        param = {}

                    rs = execute_sql(oraConn.get(j + 1), sql, param)

                    count = rs[0][0]
                    check_result.append(count)

                resultOut.append(check_result)
        except Exception as e:
            current_app.logger.error('----------------------------Exception e.message: %s', e.message)
            raise Exception(e.message)

    elif name == 'check_score_balance':
        try:
            for i in range(0, 4):
                sql = pra_sql[i] + currentMonStr
                rs = execute_sql(oraConn.get(i + 1), sql)
                count = rs[0][0]
                resultOut.append(count)
            current_app.logger.info(resultOut)
        except Exception as e:
            current_app.logger.error('----------------------------Exception e.message: %s', e.message)
            raise Exception(e.message)

    elif name == 'check_user_rel':
        try:
            for i in range(0, 4):
                sql = pra_sql[i]
                rs = execute_sql(oraConn.get(i + 1), sql)
                count = rs[0][0]
                resultOut.append(count)
            current_app.logger.info(resultOut)
        except Exception as e:
            current_app.logger.error('----------------------------Exception e.message: %s', e.message)
            raise Exception(e.message)

    elif name == 'check_score_result':
        try:
            for i in range(0, 4):
                sql = pra_sql[i][0] + currentMonStr + pra_sql[i][1] + lastMonStr + pra_sql[i][2]
                rs = execute_sql(oraConn.get(i + 1), sql)
                count = rs[0][0]
                resultOut.append(count)
            current_app.logger.info(resultOut)
        except Exception as e:
            current_app.logger.error('----------------------------Exception e.message: %s', e.message)
            raise Exception(e.message)

    elif name == 'count_score_result':
        try:
            for i in range(0, 4):
                sql = pra_sql[i] + currentMonStr
                rs = execute_sql(oraConn.get(i + 1), sql)
                count = rs[0][0]
                resultOut.append(count)
            current_app.logger.info(resultOut)
        except Exception as e:
            current_app.logger.error('----------------------------Exception e.message: %s', e.message)
            raise Exception(e.message)

    elif name == 'writeoff_users_counts':
        try:
            for i in range(0, 4):
                sql = pra_sql[i]
                param = {'param': currentMonStr}
                rs = execute_sql(oraConn.get(i + 1), sql, param)
                count = rs[0][0]
                resultOut.append(count)
            current_app.logger.info(resultOut)
        except Exception as e:
            current_app.logger.error('----------------------------Exception e.message: %s', e.message)
            raise Exception(e.message)

    elif name == 'writeoff_fee_counts':
        try:
            for i in range(0, 4):
                sql = pra_sql[i]
                param = {'param': currentMonStr}
                rs = execute_sql(oraConn.get(i + 1), sql, param)
                count = rs[0][0]
                resultOut.append(count)
            current_app.logger.info(resultOut)
        except Exception as e:
            current_app.logger.error('----------------------------Exception e.message: %s', e.message)
            raise Exception(e.message)

    elif name == 'check_balance_0':
        try:
            for i in range(0, 4):
                sql = pra_sql[i]
                param = {'param1': currentMonStr, 'param2': currentMonStr}
                rs = execute_sql(oraConn.get(i + 1), sql, param)
                count = rs[0][0]
                resultOut.append(count)
            current_app.logger.info(resultOut)
        except Exception as e:
            current_app.logger.error('----------------------------Exception e.message: %s', e.message)
            raise Exception(e.message)

    elif name == 'check_balance_1':
        try:
            for i in range(0, 4):
                sql = pra_sql[i][0] + lastMonStr + pra_sql[i][1] + currentMonStr + pra_sql[i][2]
                param = {'param1': firstDay, 'param2': currentMonStr, 'param3': firstDay}
                rs = execute_sql(oraConn.get(i + 1), sql, param)
                count = rs[0][0]
                resultOut.append(count)
            current_app.logger.info(resultOut)
        except Exception as e:
            current_app.logger.error('----------------------------Exception e.message: %s', e.message)
            raise Exception(e.message)

    elif name == 'check_balance_2':
        # 获取当前月份
        currentMonStr = getCurrentMon()
        current_app.logger.info(currentMonStr)
        lastYearMonStr = getLastYearMon()
        current_app.logger.info(lastYearMonStr)

        try:
            for i in range(0, 4):
                sql = pra_sql[i][0] + lastYearMonStr + pra_sql[i][1]
                param = {'param1': lastYearMonStr, 'param2': currentMonStr}
                rs = execute_sql(oraConn.get(i + 1), sql, param)
                count = rs[0][0]
                resultOut.append(count)
            current_app.logger.info(resultOut)
        except Exception as e:
            current_app.logger.error('----------------------------Exception e.message: %s', e.message)
            raise Exception(e.message)

    elif name == 'check_balance_3':
        try:
            for i in range(0, 4):
                sql = pra_sql[i]
                param = {'param1': lastMonStr, 'param2': lastMonStr, 'param3': currentMonStr}
                rs = execute_sql(oraConn.get(i + 1), sql, param)
                count = rs[0][0]
                resultOut.append(count)
            current_app.logger.info(resultOut)
        except Exception as e:
            current_app.logger.error('----------------------------Exception e.message: %s', e.message)
            raise Exception(e.message)

    elif name == 'check_balance_4':
        try:
            for i in range(0, 4):
                sql = pra_sql[i][0] + lastMonStr + pra_sql[i][1] + currentMonStr + pra_sql[i][2]
                param = {'param1': lastYearMonStr}
                rs = execute_sql(oraConn.get(i + 1), sql, param)
                count = rs[0][0]
                resultOut.append(count)
            current_app.logger.info(resultOut)
        except Exception as e:
            current_app.logger.error('----------------------------Exception e.message: %s', e.message)
            raise Exception(e.message)

    elif name == 'check_balance_5':
        try:
            for i in range(0, 4):
                sql = pra_sql[i][0] + currentMonStr + pra_sql[i][1] + currentMonStr + pra_sql[i][2]
                rs = execute_sql(oraConn.get(i + 1), sql)
                count = rs[0][0]
                resultOut.append(count)
            current_app.logger.info(resultOut)
        except Exception as e:
            current_app.logger.error('----------------------------Exception e.message: %s', e.message)
            raise Exception(e.message)

    elif name == 'check_balance_6':
        try:
            for i in range(0, 4):
                sql = pra_sql[i][0] + currentMonStr + pra_sql[i][1] + currentMonStr + pra_sql[i][2]
                param = {'param1': firstDay}
                rs = execute_sql(oraConn.get(i + 1), sql, param)
                count = rs[0][0]
                resultOut.append(count)
            current_app.logger.info(resultOut)
        except Exception as e:
            current_app.logger.error('----------------------------Exception e.message: %s', e.message)
            raise Exception(e.message)

    elif name == 'check_calc_log_t':
        try:
            for i in range(0, 4):
                sql = pra_sql[i]
                rs = execute_sql(oraConn.get(i + 1), sql)
                count = rs[0][0]
                resultOut.append(count)
            current_app.logger.info(resultOut)
        except Exception as e:
            current_app.logger.error('----------------------------Exception e.message: %s', e.message)
            raise Exception(e.message)

    elif name == 'check_calc_log_mm':
        try:
            for i in range(0, 4):
                sql = pra_sql[i] + currentMonStr
                rs = execute_sql(oraConn.get(i + 1), sql)
                count = rs[0][0]
                resultOut.append(count)
            current_app.logger.info(resultOut)
        except Exception as e:
            current_app.logger.error('----------------------------Exception e.message: %s', e.message)
            raise Exception(e.message)

    elif name == 'check_balance_calc_mm':
        try:
            for i in range(0, 4):
                sql = pra_sql[i] + currentMonStr
                rs = execute_sql(oraConn.get(i + 1), sql)
                count = rs[0][0]
                resultOut.append(count)
            current_app.logger.info(resultOut)
        except Exception as e:
            current_app.logger.error('----------------------------Exception e.message: %s', e.message)
            raise Exception(e.message)

    elif name == 'score_effect_check':
        try:
            for i in range(0, 4):
                sql = pra_sql[i][0]
                rs = execute_sql(oraConn.get(i + 1), sql)
                count_1 = rs[0][0]

                sql = pra_sql[i][1] + currentMonStr
                rs = execute_sql(oraConn.get(i + 1), sql)
                count_2 = rs[0][0]

                sql = pra_sql[i][2] + currentMonStr
                rs = execute_sql(oraConn.get(i + 1), sql)
                count_3 = rs[0][0]

                if count_1 == count_2 and count_2 == count_3:
                    count = 0
                else:
                    count = 1

                resultOut.append(count)
            current_app.logger.info(resultOut)
        except Exception as e:
            current_app.logger.error('----------------------------Exception e.message: %s', e.message)
            raise Exception(e.message)

    elif name == 'backup_progress':
        try:
            sql = pra_sql
            datas = execute_sql(oraConnPARAM, sql)
            current_app.logger.info('----------------------------rs.fetchall(): %s', datas)
            for i in range(0, len(datas)):
                work_id = datas[i][0]
                next_date = datas[i][1]
                current_app.logger.info('----------------------------rs.fetchall()[i][0]: %s', datas[i][0])
                current_app.logger.info('----------------------------rs.fetchall()[i][1]: %s', datas[i][1])
                if str(next_date) == getNextYearMonDay():
                    result = 0
                else:
                    result = 1
                resultOut.append(str(work_id) + ':' + str(result))

            current_app.logger.info(resultOut)
        except Exception as e:
            current_app.logger.error('----------------------------Exception e.message: %s', e.message)
            raise Exception(e.message)

    elif name == 'am_balance_mm':
        try:
            for i in range(0, 4):
                sql = pra_sql[i][0] + lastMonStr + pra_sql[i][1]
                count_balance = execute_sql(oraConn.get(i + 1), sql)
                balance_sum = count_balance[0][0]
                balance_count = count_balance[0][1]

                sql = pra_sql[i][2] + lastYearMonStr + pra_sql[i][3]
                count_balance_sta = execute_sql(oraConn.get(i + 1), sql)
                balance_sta_sum = count_balance_sta[0][0]
                balance_sta_count = count_balance_sta[0][1]

                current_app.logger.info(balance_sum)
                current_app.logger.info(balance_sta_sum)
                current_app.logger.info(balance_count)
                current_app.logger.info(balance_sta_count)

                if balance_sum == balance_sta_sum and balance_count == balance_sta_count:
                    count = 0
                else:
                    count = 1

                resultOut.append(count)
            current_app.logger.info(resultOut)
        except Exception as e:
            current_app.logger.error('----------------------------Exception e.message: %s', e.message)
            raise Exception(e.message)

    elif name == 'ag_balance_mm':
        try:
            for i in range(0, 4):
                sql = pra_sql[i][0]
                count_balance = execute_sql(oraConn.get(i + 1), sql)
                balance_sum = count_balance[0][0]
                balance_count = count_balance[0][1]

                sql = pra_sql[i][1] + lastYearMonStr + pra_sql[i][2]
                count_balance_sta = execute_sql(oraConn.get(i + 1), sql)
                balance_sta_sum = count_balance_sta[0][0]
                balance_sta_count = count_balance_sta[0][1]

                if balance_sum == balance_sta_sum and balance_count == balance_sta_count:
                    count = 0
                else:
                    count = 1

                resultOut.append(count)
            current_app.logger.info(resultOut)
        except Exception as e:
            current_app.logger.error('----------------------------Exception e.message: %s', e.message)
            raise Exception(e.message)

    elif name == 'sc_balance_mm':
        try:
            for i in range(0, 4):
                sql = pra_sql[i][0] + lastYearMonStr + pra_sql[i][1]
                rs = execute_sql(oraConn.get(i + 1), sql)
                acct_user_balance_1 = rs[0][0]

                sql = pra_sql[i][2]
                rs = execute_sql(oraConn.get(i + 1), sql)
                sc_balance_1 = rs[0][0]

                if acct_user_balance_1 == sc_balance_1:
                    count = 0
                else:
                    count = 1

                resultOut.append(count)
            current_app.logger.info(resultOut)
        except Exception as e:
            current_app.logger.error('----------------------------Exception e.message: %s', e.message)
            raise Exception(e.message)

    elif name == 'am_bill_item_mm':
        try:
            for i in range(0, 4):
                sql = pra_sql[i][0] + lastYearMonStr + pra_sql[i][1]
                count_bill_item_sta = execute_sql(oraConn.get(i + 1), sql)
                bill_item_sta_sum = count_bill_item_sta[0][0]
                bill_item_sta_count = count_bill_item_sta[0][1]

                sql = pra_sql[i][2]
                count_bill_item = execute_sql(oraConn.get(i + 1), sql)
                bill_item_sum = count_bill_item[0][0]
                bill_item_count = count_bill_item[0][1]

                if bill_item_sta_sum == bill_item_sum and bill_item_sta_count == bill_item_count:
                    count = 0
                else:
                    count = 1

                resultOut.append(count)
            current_app.logger.info(resultOut)
        except Exception as e:
            current_app.logger.error('----------------------------Exception e.message: %s', e.message)
            raise Exception(e.message)
        else:
            pass

    return resultOut


# 积分生效修改参数
def update_param(param, pra_sql):
    try:
        param = {'param': param}
        sql = pra_sql
        execute_sql(oraConnPARAM, sql, param)
    except Exception as e:
        current_app.logger.error('----------------------------Exception e.message: %s', e.message)
        raise Exception(e.message)
    finally:
        pass


# 获取当月第一天
def getMonthFirstDay(year=None, month=None):
    """
    :param year: 年份，默认是本年，可传int或str类型
    :param month: 月份，默认是本月，可传int或str类型
    :return: firstDay: 当月的第一天，datetime.date类型
    """
    if year:
        year = int(year)
    else:
        year = datetime.date.today().year

    if month:
        month = int(month)
    else:
        month = datetime.date.today().month

    # 获取当月的第一天
    firstDay = datetime.date(year=year, month=month, day=1)

    return firstDay


# 获取当月最后一天
def getMonthLastDay(year=None, month=None):
    if year:
        year = int(year)
    else:
        year = datetime.date.today().year

    if month:
        month = int(month)
    else:
        month = datetime.date.today().month

    # 获取当月第一天的星期和当月的总天数
    firstDayWeekDay, monthRange = calendar.monthrange(year, month)
    # 获取当月的最后一天
    lastDay = datetime.date(year=year, month=month, day=monthRange)

    return lastDay


# 获取当前 月份(mm)
def getCurrentMon():
    firstDay = getMonthFirstDay()
    currentMon = firstDay.strftime('%m')
    return currentMon


# 获取上月 月份(mm)
def getLastMon():
    firstDay = getMonthFirstDay()
    lastMonLastDay = firstDay - datetime.timedelta(days=1)
    lastMon = lastMonLastDay.strftime('%m')
    return lastMon


# 获取上月 年月份(yyyymm)
def getLastYearMon():
    firstDay = getMonthFirstDay()
    lastMonLastDay = firstDay - datetime.timedelta(days=1)
    lastYearMon = lastMonLastDay.strftime('%Y%m')
    return lastYearMon


# 获取上月 年月日(yyyymmdd)
def getLastYearMonDay():
    firstDay = getMonthFirstDay()
    lastMonLastDay = firstDay - datetime.timedelta(days=1)
    lastYearMonDayStr = lastMonLastDay.strftime('%Y%m%d')
    return lastYearMonDayStr


# 获取上月 年月日时分秒(yyyymmdd hh24miss)
def getLastYearMonDayH():
    firstDay = getMonthFirstDay()
    lastMonLastDay = firstDay - datetime.timedelta(days=1)
    LastYearMonDayHStr = lastMonLastDay.strftime('%Y%m%d %H%M%S')
    return LastYearMonDayHStr


# 获取下月第一天(yyyymmdd)
def getNextYearMonDay():
    lastDay = getMonthLastDay()
    nextYearMonDay = lastDay + datetime.timedelta(days=1)
    nextYearMonDayStr = nextYearMonDay.strftime('%Y%m%d')
    return nextYearMonDayStr
